package com.querie.beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * This class
 * @author manish.mittal
 *
 */
public class DatabaseAccess {
	private static DatabaseAccess INSTANCE = null;
	Connection mySQLConnection = null;
	Connection myPostgresConnection = null;
	// Variable added for debugging purpose only.
	long count = 0;
	
	public static DatabaseAccess getInstance() throws Exception {
    	if (INSTANCE == null) {
    		INSTANCE = new DatabaseAccess();
    	}
    	
    	return INSTANCE;
	}
	
	/**
	 * Constructor
	 * @throws Exception
	 */
	private DatabaseAccess() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");		
		mySQLConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/querie", "root", "root");
	}
	
	/**
	 * This is used initially to create hash functions and store their parameters in DB. The same values
	 * are used every time min hash vaue is calculated.
	 * 
	 * @param numOfHashFunctions
	 * @param hashFunctions
	 * @throws Exception
	 */
	public void saveHashFunctionsToDB(int numOfHashFunctions, HashFunctions hashFunctions) throws Exception {
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			StringBuilder builder = new StringBuilder();
			
			for (int i = 0; i < numOfHashFunctions; i++) {
				builder.append("insert into hashFunctions(a_s, b_s, c_s) values('");
				builder.append(hashFunctions.getA_s()[i]);
				builder.append("','");
				builder.append(hashFunctions.getB_s()[i]);
				builder.append("','");
				builder.append(hashFunctions.getC_s()[i]);
				builder.append("')");
				
				stmt.executeUpdate(builder.toString());
				builder.setLength(0);
			}
		
		}
	}
	
	/**
	 * Retrives hash function paramaters from database.
	 * @param numOfHashFunctions
	 * @return HashFunctions.
	 * @throws Exception
	 */
	public HashFunctions getHashFunctionsFromDB(int numOfHashFunctions) throws Exception {
		HashFunctions hashFunctions = null;
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();				
			String string = "select * from hashFunctions";

			ResultSet rs = stmt.executeQuery(string);
			
			if (rs.first() == true) {
				hashFunctions = new HashFunctions(numOfHashFunctions);
				
				int i = 0;
				hashFunctions.getA_s()[i] = rs.getLong(1);
				hashFunctions.getB_s()[i] = rs.getLong(2);
				hashFunctions.getC_s()[i] = rs.getLong(3);
				i++;
				while(rs.next() == true) {
					hashFunctions.getA_s()[i] = rs.getLong(1);
					hashFunctions.getB_s()[i] = rs.getLong(2);
					hashFunctions.getC_s()[i] = rs.getLong(3);
					i++;
				}
			}
		}
		
		return hashFunctions;
	}

	/**
	 * Gets all the query strings posed by a user.
	 * @param sessionId
	 * @return List of query strings.
	 * @throws Exception
	 */
	public List<String> getQueriesForSessionId(int sessionId) throws Exception {
		List<String> output = new ArrayList<String>();

		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			StringBuilder builder = new StringBuilder();
			builder.append("select queryString from sessionqueries where sessionid = '");
			builder.append(sessionId);
			builder.append("'");
			
			ResultSet rSet = stmt.executeQuery(builder.toString());
			
			while(rSet.next() == true) {
				String query = rSet.getString(1);
				output.add(query);
			}
			rSet.close();
			stmt.close();
			builder = null;
		}

        return output;
	}
	
	/**
	 * This function is used to save min hash values in the database for the given session id.
	 * If insertNew is true, then a new row is inserted else the same row is updated.
	 * 
	 * @param sessionId
	 * @param minHashValues
	 * @param insertNew
	 * @throws Exception
	 */
	public void saveMinHashValues(int sessionId, List<Long> minHashValues, boolean insertNew) throws Exception {
	
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			StringBuilder minHashValuesAsString = new StringBuilder();
			
			for (Long minHashValue: minHashValues) {
				minHashValuesAsString.append(String.valueOf(minHashValue));
				minHashValuesAsString.append(",");
			}
			
			minHashValuesAsString.deleteCharAt(minHashValuesAsString.length() - 1);
			
			StringBuilder builder = new StringBuilder();
			if (insertNew == true) {
				builder.append("insert into minhashmatrix(sessionId, minhashvalues) values('");
				builder.append(sessionId);
				builder.append("','");
				builder.append(minHashValuesAsString);
				builder.append("')");
			}
			else {
				builder.append("update minhashmatrix set minhashvalues = '");
				builder.append(minHashValuesAsString);
				builder.append("' where sessionid = '");
				builder.append(sessionId);
				builder.append("'");
			}
			
			//System.out.println(builder.toString());
			
			stmt.executeUpdate(builder.toString());
		}
	}
	
	/**
	 * This returns the min hash values for a given sessionid.
	 * @param sessionId
	 * @return min hash values.
	 * @throws Exception
	 */
	public List<Long> getMinHashValues(int sessionId) throws Exception {

		List<Long> minHashValues = null;
		String minhashValuesAsString = getMinHashValuesAsString(sessionId);
				
		// Convert the tupleIds from comma separated string to list of tuple ids.
		if(minhashValuesAsString != null)
		{
			String[] tokens = minhashValuesAsString.split(",");
			
			if (tokens != null && tokens.length > 0) {
				minHashValues = new ArrayList<Long>();
				
				for (int i = 0; i < tokens.length; i++) {
					minHashValues.add(Long.parseLong(tokens[i]));
				}
			}
			//System.out.println(minHashValues);
		}
		return minHashValues;
	}
	
	public String getMinHashValuesAsString(int sessionId) throws Exception {
		String minhashValuesAsString = null;
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			StringBuilder builder = new StringBuilder();
			builder.append("select minhashvalues from minhashmatrix where sessionid = '");
			builder.append(sessionId);
			builder.append("'");
			
			//System.out.println(builder.toString());
			
			ResultSet rs = stmt.executeQuery(builder.toString());
			
			if (rs.first() == true) {
				rs.first();
				minhashValuesAsString = rs.getString(1);
			}
		}
		
		return minhashValuesAsString;
	}
	
	/**
	 * Saves the userid in the given bucket name.
	 * 
	 * @param bucketName
	 * @param sessionId
	 * @throws Exception
	 */
	public void saveLSHBucket(String bucketName, Integer sessionId) throws Exception {
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			String prevSessionIdsAsString = this.getLSHBucket(bucketName);
			StringBuilder newSessionIdsAsString = new StringBuilder();
			
			if (prevSessionIdsAsString != null) {
				newSessionIdsAsString.append(prevSessionIdsAsString);
				newSessionIdsAsString.append(",");
			}
			
			newSessionIdsAsString.append(sessionId);
			
			StringBuilder builder = new StringBuilder();
			
			// If no prev record found, insert new else update.
			if (prevSessionIdsAsString == null) {
				builder.append("insert into lshbuckets(bucketName, sessionIds) values('");
				builder.append(bucketName);
				builder.append("','");
				builder.append(newSessionIdsAsString);
				builder.append("')");
			}
			else {
				builder.append("update lshbuckets set sessionIds='");
				builder.append(newSessionIdsAsString);
				builder.append("' where bucketname='");
				builder.append(bucketName);
				builder.append("'");
			}
			
			//System.out.println(builder.toString());			
			stmt.executeUpdate(builder.toString());
		}
	}
	
	/**
	 * Gets the session ids as string for the given LSH bucket name.
	 * @param bucketName
	 * @return session ids as string
	 * @throws Exception
	 */
	public String getLSHBucket(String bucketName) throws Exception {
		String sessionIdsAsString = null;
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			StringBuilder builder = new StringBuilder();
			builder.append("select sessionIds from lshbuckets where bucketName = '");
			builder.append(bucketName);
			builder.append("'");
			
			//System.out.println(builder.toString());
			
			ResultSet rs = stmt.executeQuery(builder.toString());
			
			if (rs.first() == true) {
				sessionIdsAsString = rs.getString(1);
			}
		}
			
		return sessionIdsAsString;
	}
	
	/**
	 * Gets the session ids in the given LSH bucket as a list.
	 * @param bucketName
	 * @return List of session ids.
	 * @throws Exception
	 */
	public List<Integer> getSetIdsInBucket(String bucketName) throws Exception {
		String sessionIdsAsString = this.getLSHBucket(bucketName);
		List<Integer> setIds = null;
		
		String[] tokens = null;
		if(sessionIdsAsString != null)
			tokens = sessionIdsAsString.split(",");
		
		if (tokens != null && tokens.length > 0) {
			setIds = new ArrayList<Integer>();
			
			for (int i = 0; i < tokens.length; i++) {
				setIds.add(Integer.parseInt(tokens[i]));
			}
		}
		
		return setIds;
	}

	/**
	 * This function reads all the tuple ids touched by a user from postgres db.
	 * @param sessionId
	 * @return list of tuple ids.
	 * @throws Exception
	 */
	public List<Long> readTupleIdsFromMySQL(int sessionId) throws Exception {
		List<Long> tupleIds = new ArrayList<Long>();
		List<Integer> queryIds = this.readQueryIdsFromMySQL(sessionId);
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			for (Integer queryId : queryIds) {
				String sQuery = "select tupleId from queryresults where queryId=" + queryId;
				ResultSet rSet = stmt.executeQuery(sQuery);
				
				while(rSet.next() == true) {
					long tupleId = rSet.getLong(1);
					
					tupleIds.add(tupleId);
				}
				rSet.close();
				sQuery = null;
			}
			
			stmt.close();
			//conn.close();
		}
		
		return tupleIds;
	}
	
	/**
	 * This reads the tuple ids for a given query id from postgres.
	 * @param queryId
	 * @return set of tuple ids.
	 * @throws Exception
	 */
	public Set<Long> readTupleIdsForQueryFromMySQL(int queryId) throws Exception {
		Set<Long> tupleIds = new HashSet<Long>();
	
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();

			String sQuery = "select tupleId from queryresults where queryId=" + queryId;
			ResultSet rSet = stmt.executeQuery(sQuery);
			
			while(rSet.next() == true) {
				long tupleId = rSet.getLong(1);
				
				tupleIds.add(tupleId);
			}
			rSet.close();
			sQuery = null;
			stmt.close();
			//conn.close();
		}
		
		return tupleIds;
	}

	/**
	 * This reads the query ids from postgres for a given session id.
	 * @param sessionId
	 * @return list of query ids.
	 * @throws Exception
	 */
	public List<Integer> readQueryIdsFromMySQL(int sessionId) throws Exception {
		List<Integer> queryIds = new ArrayList<Integer>();
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			String sQuery = "select queryId from sessionqueries where sessionId=" + sessionId;

			ResultSet rSet = stmt.executeQuery(sQuery);
			
			while(rSet.next() == true) {
				int queryId = rSet.getInt(1);
				
				queryIds.add(queryId);
			}
			rSet.close();
			stmt.close();
			//conn.close();
			sQuery = null;
		}
		
		return queryIds;
	}
	
	/**
	 * This gets the query string for a given query id from postgres.
	 * @param queryId
	 * @return query string
	 * @throws Exception
	 */
	public List<String> readQueryStringForQueryIdFromMySQL(int queryId) throws Exception {
		List<String> queryObjects = new ArrayList<String>();
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			String sQuery = "select sessionId, queryId, queryString from sessionqueries where queryId=" + queryId;

			ResultSet rSet = stmt.executeQuery(sQuery);
			
			while(rSet.next() == true) {
				queryObjects.add(rSet.getString(1));
				queryObjects.add(rSet.getString(2));
				queryObjects.add(rSet.getString(3));
			}
			rSet.close();
			stmt.close();
			//conn.close();
			sQuery = null;
		}
		
		return queryObjects;
	}
	
	
	/**
	 * This reads all the session ids from MySQL db.
	 * @return List of session ids.
	 * @throws Exception
	 */
	public List<Integer> readSessionIds() throws Exception {
		List<Integer> sessionIds = new ArrayList<Integer>();
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			String sQuery = "select DISTINCT sessionId from minhashmatrix order by sessionId";

			ResultSet rSet = stmt.executeQuery(sQuery);
			
			while(rSet.next() == true) {
				int sessionId = rSet.getInt(1);
				sessionIds.add(sessionId);
			}
			rSet.close();
			stmt.close();
			sQuery = null;
		}
		
		return sessionIds;
	}
	
	
	/**
	 * This function saves the tuple ids for a given session into MySQL.
	 * @param sessionId
	 * @param queryStrings
	 * @throws Exception
	 */
	public void saveTupleIdsToMySQL(int sessionId, String userQuery, List<Long> tupleIds) throws Exception {
		
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement();
			
			StringBuilder builder = new StringBuilder();
			
			String[] tokens = userQuery.split("\'");
			StringBuilder newStr = new StringBuilder();
			if (tokens != null && tokens.length > 0) {
				for (int i = 0; i < tokens.length - 1; i++) {
					newStr.append(tokens[i]);
					newStr.append("\\'");
				}
				newStr.append(tokens[tokens.length - 1]);
			}
			int queryId = this.generateNewQueryId();
			
			System.out.println("New query id: " + queryId);
			System.out.println("New query string: " + userQuery);
			
			builder.append("insert into sessionqueries(sessionId, queryString, queryId) values('");
			builder.append(sessionId);
			builder.append("','");
			builder.append(newStr.toString());
			builder.append("','");
			builder.append(queryId);
			builder.append("')");
			
			stmt.executeUpdate(builder.toString());
			builder.setLength(0);
			
			Iterator<Long> it = tupleIds.iterator();
			
			while (it.hasNext()) {
				Long tupleId = it.next();
				builder.append("insert into queryresults(queryId, tupleId) values('");
				builder.append(queryId);
				builder.append("','");
				builder.append(tupleId);
				builder.append("')");
				
				stmt.executeUpdate(builder.toString());
				builder.setLength(0);
			}
		}
	}
	
	/**
	 * This function generates a new session id when a new user logs in.
	 * @return new session id.
	 * @throws Exception
	 */
	public int generateNewSessionId() throws Exception {
		int sessionId = 0;
                String sQuery = null;
                String collab = com.querie.util.QryParameters.getInstance().getValue("ITEMORUSER");
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			if (collab.equals("item")){
                            sQuery = "select MAX(sessionId) as maxSessionId from sessionqueries";
                        }else{
                            sQuery = "select MAX(sessionId) as maxSessionId from minhashmatrix";
                        }

			ResultSet rSet = stmt.executeQuery(sQuery);
		
			if (rSet != null && rSet.first() == true) {
				rSet.first();
				sessionId = rSet.getInt(1);
				sessionId++;
			}
			rSet.close();
			stmt.close();
		}
		
		return sessionId;
	}
	
	/**
	 * This function generates a new query id.
	 * @return new session id.
	 * @throws Exception
	 */
	public int generateNewQueryId() throws Exception {
		int queryId = 0;
		if(mySQLConnection != null)
		{
			Statement stmt = mySQLConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			String sQuery = "select MAX(queryid) from sessionqueries;";

			ResultSet rSet = stmt.executeQuery(sQuery);
		
			if (rSet != null && rSet.first() == true) {
				rSet.first();
				queryId = rSet.getInt(1);
				queryId++;
			}
			rSet.close();
			stmt.close();
		}
		
		return queryId;
	}
	
	/**
	 * This function gets all the queries for the users in the given list of session ids.
	 * @param sSessionIds
	 * @return List of query strings.
	 * @throws Exception
	 */
	public ArrayList<String> getRecommendedQueries(Set<Integer> sSessionIds) throws Exception {
		ArrayList<String> aList = new ArrayList<String>();

		int i = 0;
		if(mySQLConnection != null)
		{
			
			Statement stmt = mySQLConnection.createStatement();
			
			Iterator<Integer> it = sSessionIds.iterator();
			String sIds = "(";
			
			while(it.hasNext())
			{
				sIds = sIds + it.next() + ",";
			}
			sIds = sIds.substring(0, sIds.length()-1) + ")";
			
			String sQuery = "select queryString from sessionqueries where sessionId in ";
			sQuery = sQuery + sIds; 
			System.out.println(sQuery);
			ResultSet rSet = stmt.executeQuery(sQuery);
		
			while(rSet.next()) {
				aList.add(rSet.getString(1));
				i = i + 1;
				if(i > 4)
					break;
	
			}
			rSet.close();
			stmt.close();
		}
		
		return aList;
	}
	
	/**
	 * This funcion is used to compute predicted vector for a new user.
	 * @param newSetId
	 * @param similarSetIds
	 * @return Predicted vector.
	 * @throws Exception
	 */
	public Set<Long> computePredictedVector(Integer newSetId, Set<Integer> similarSetIds) throws Exception {
		Iterator<Integer> it = similarSetIds.iterator();
		Set<Long> predictedVector = new HashSet<Long>();
		
		while (it.hasNext()) {
			List<Long> tupleIds = this.readTupleIdsFromMySQL(it.next());
			
			predictedVector.addAll(tupleIds);
		}
		
		predictedVector.addAll(this.readTupleIdsFromMySQL(newSetId));
		
		return predictedVector;
	}
	
	/**
	 * This function is used to calculate similar queries for a user.
	 * 
	 * @param predictedVector
	 * @param newSetId
	 * @param similarSetIds
	 * @return List of similar queries.
	 * @throws Exception
	 */
	public List<String> findSimilarQueries(Set<Long> predictedVector,
			Integer newSetId, Set<Integer> similarSetIds,
			List<Long> evaluationTupleIds) throws Exception {
		List<String> similarQueries = new ArrayList<String>();
		Set<Integer> allQueryIds = new HashSet<Integer>();
		Map<Integer, Float> similarityValues = new HashMap<Integer, Float>();
		
		Iterator<Integer> it = similarSetIds.iterator();
		while (it.hasNext()) {
			List<Integer> queryIds = this.readQueryIdsFromMySQL(it.next());
			
			allQueryIds.addAll(queryIds);
		}
		
		for (Integer queryId: allQueryIds) {
			Set<Long> tupleIdsForQuery = readTupleIdsForQueryFromMySQL(queryId);
			
			float similarity = compareQueryWithPredictedVector(predictedVector, tupleIdsForQuery);
			similarityValues.put(queryId, similarity);
		}
		
		Collection<Float> values = similarityValues.values();
		Set<Float> similarities = new HashSet<Float>();
		similarities.addAll(values);
		
		List<Float> maxSimilarities = new ArrayList<Float>();
		for (int i = 0; i < 5; i++) {
			Float max = this.maxSimilarity(similarities);
			similarities.remove(max);
			
			if (max != 0.0) { 
				maxSimilarities.add(max);
			}
		}
		
		Map<Integer, Float> similarQueryIds = this.getQueryIdsWithMaxSimilarities(similarityValues,
				maxSimilarities);
		
		Map<Integer, Float> recallValues = new HashMap<Integer, Float>();
		Map<Integer, Float> precisionvalues = new HashMap<Integer, Float>();
		
		Set<Integer> keyValues = similarQueryIds.keySet();
		for (Integer queryId: keyValues) {
			similarQueries.addAll(readQueryStringForQueryIdFromMySQL(queryId));
			String simValue = Float.toString((Float)similarQueryIds.get(queryId));
			
			similarQueries.add(simValue);
			
			// This part is just for evaluation of query recommendations.
			if (evaluationTupleIds != null && evaluationTupleIds.size() > 0) {
				Set<Long> tupleIdsForQuery = readTupleIdsForQueryFromMySQL(queryId);
				Set<Long> evaluationSet = new HashSet<Long>();
				evaluationSet.addAll(evaluationTupleIds);
				
				float recall = this.computePrecisionRecall(evaluationSet, tupleIdsForQuery, false);
				float precision = this.computePrecisionRecall(evaluationSet, tupleIdsForQuery, true);
				recallValues.put(queryId, recall);
				precisionvalues.put(queryId, precision);
			}
		}
		System.out.println("Recall values: " + recallValues);
		System.out.println("Precision values: " + precisionvalues);
		
		System.out.println("Similar queryIds: " + similarQueryIds);
		System.out.println("Recommended queries: " + similarQueries);
		
		return similarQueries;
		
	}
	
	
	/**
	 * This is a function to find the maximum similarity value out of a given set of values.
	 * 
	 * @param similarities
	 * @return Maximum similarity value.
	 */
	private float maxSimilarity(Set<Float> similarities) {
		float max = 0;
		Iterator<Float> it = similarities.iterator();
		
		while(it.hasNext()) {
			float next = it.next();
			if (max < next) {
				max = next;
			}
		}
		
		return max;
	}
	
	/**
	 * This function gets the query ids with maximum similarity values.
	 * 
	 * @param similarityValueMap
	 * @param maxSimilarities
	 * @return List of query ids.
	 */
	private Map<Integer, Float> getQueryIdsWithMaxSimilarities(Map<Integer, Float> similarityValueMap,
			List<Float> maxSimilarities) {
		Map<Integer, Float> similarQueryIds = new HashMap<Integer, Float>();
		
		label1: for (Float maxSimilarity: maxSimilarities) {
			Iterator<Integer> it = similarityValueMap.keySet().iterator();
			while(it.hasNext()) {
				Integer key = it.next();
				if (similarityValueMap.get(key).equals(maxSimilarity)) {
					similarQueryIds.put(key, maxSimilarity);
				}
				
				if (similarQueryIds.size() >= 10) {
					break label1;
				}
			}
		}
		
		return similarQueryIds;
	}
	
	/**
	 * This function is used to compare a query vector with the predicted vector to find the similarity value.
	 * 
	 * @param predictedVector
	 * @param tupleIdsForQuery
	 * @return Similarity value.
	 */
	public float compareQueryWithPredictedVector(Set<Long> predictedVector, Set<Long> tupleIdsForQuery) {
		float similarity = 0;
		Iterator<Long> it = tupleIdsForQuery.iterator();
		while (it.hasNext()) {
			if (predictedVector.contains(it.next())) {
				similarity++;
			}
		}
		if (tupleIdsForQuery.size() > 0) {
			similarity = (similarity/predictedVector.size()) * 100;
		}
		
		return similarity;
	}
	
	public float computePrecisionRecall(Set<Long> evaluationVector, Set<Long> tupleIdsForQuery, boolean isPrecision) {
		float similarity = 0;
		Iterator<Long> it = tupleIdsForQuery.iterator();
		
		while (it.hasNext()) {
			if (evaluationVector.contains(it.next())) {
				similarity++;
			}
		}
		if (isPrecision == true && tupleIdsForQuery.size() > 0) {
			similarity = (similarity/tupleIdsForQuery.size()) * 100;
		}
		else if (isPrecision == false && evaluationVector.size() > 0) {
			similarity = (similarity/evaluationVector.size()) * 100;
		}
		
		return similarity;
	}
	
	public void closeConnection() throws Exception {
		mySQLConnection.close();
	}
}
